package controllers.dbWrapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.GregorianCalendar;

import person.Customer;
import budget.Request;
import controllers.ExceptionsTypes;
import event.Event;

public class RequestWrapper {
	
	public static ArrayList<Request> getAllRequest(boolean closeConnection) throws Exception{
		ArrayList<Request> requests = new ArrayList<Request>();
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".budget_request ";
	   
	    Statement stmt = DBWrapper.getConnection().createStatement();
	    ResultSet rs = stmt.executeQuery(query);
	    while (rs.next()) {
	    	long idRequest = rs.getInt("id_budget_request");
	    	long dataInMillis = rs.getLong("budget_request_date");
	    	long idEvent = rs.getInt("id_event");
	    	String customer = rs.getString("customer_tax_code");
	    	GregorianCalendar date = new GregorianCalendar();
	    	date.setTimeInMillis(dataInMillis);
	    	String memo = rs.getString("budget_request_memo");
	    	Request r = new Request(idRequest, date, EventWrapper.getEvent(idEvent, false), memo, CustomerWrapper.getCustomer(customer, false));
	    	try{
	    		BudgetWrapper.getBudgetByRequest(r, false);
	    	}catch (Exception e) {
	    		if(e.getMessage().equalsIgnoreCase(ExceptionsTypes.NOT_FOUND.name()))
	    			requests.add(r);
			}
	    }
	    stmt.close();
	    if(closeConnection)
	    	DBWrapper.getConnection().close();
	    return requests;
	}
	
	public static Request getRequest(long idRequest, boolean closeConnection) throws Exception{
		Request r = null;
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".budget_request " +
	    		"WHERE id_budget_request = "+idRequest;
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        if(rs.next()){
	        	long dataInMillis = rs.getLong("budget_request_date");
	        	long idEvent = rs.getInt("id_event");
	        	String customer = rs.getString("customer_tax_code");
	        	GregorianCalendar date = new GregorianCalendar();
	        	date.setTimeInMillis(dataInMillis);
	        	String memo = rs.getString("budget_request_memo");
	        	r = new Request(idRequest, date, EventWrapper.getEvent(idEvent, false), memo, CustomerWrapper.getCustomer(customer, false));
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    if(r == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return r;
	}
	
	public static ArrayList<Request> getRequest(Customer customer, boolean closeConnection) throws Exception{
		ArrayList<Request> requests = new ArrayList<Request>();
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".budget_request " +
	    		"WHERE customer_tax_code = '"+customer.getTaxCode()+"'";
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        while(rs.next()){
	        	long dataInMillis = rs.getLong("budget_request_date");
	        	long idRequest = rs.getInt("id_budget_request");
	        	long idEvent = rs.getInt("id_event");
	        	GregorianCalendar date = new GregorianCalendar();
	        	date.setTimeInMillis(dataInMillis);
	        	String memo = rs.getString("budget_request_memo");
	        	Request r = new Request(idRequest, date, EventWrapper.getEvent(idEvent, false), memo, customer);
	        	try{
		    		BudgetWrapper.getBudgetByRequest(r, closeConnection);
		    	}catch (Exception e) {
		    		if(e.getMessage().equalsIgnoreCase(ExceptionsTypes.NOT_FOUND.name()))
		    			requests.add(r);
				}
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    return requests;
	}
	
	public static void insertRequest(boolean closeConnection, Request ... requests) throws Exception{
		
			Statement stmt = DBWrapper.getConnection().createStatement();
			for(int i = 0 ; i<requests.length; i++){
				Request r = requests[i];
				Event e = r.getEvent();
				Customer c = r.getCustomer();
				//verifico se l'evento e il customer esistono, altrimenti li inserisco
				try{
					EventWrapper.getEvent(e.getId(), false);
				}catch(Exception ex1){
					if(ex1.getMessage().equalsIgnoreCase(ExceptionsTypes.NOT_FOUND.name()))
						EventWrapper.insertEvent(false, e);
					else
						throw ex1;
				}
				try{
					CustomerWrapper.getCustomer(c.getId(), false);
				}catch(Exception ex2){
					if(ex2.getMessage().equalsIgnoreCase(ExceptionsTypes.NOT_FOUND.name()))
						CustomerWrapper.insertCustomer(false, c);
					else
						throw ex2;
				}
				int rows = stmt.executeUpdate(
			        	"INSERT INTO "+ DBWrapper.getDbname() +".budget_request (budget_request_memo, id_event, customer_tax_code, budget_request_date) " +
			        	"VALUES('"  + 
			        		r.getMemo() + "'," +
			        		e.getId()+ ",'" +
			        		c.getTaxCode() +"'," +
			        		r.getRequestDate().getTimeInMillis() +")");
				if(rows == 0)
					throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
				for(Request rq: getRequest(r.getCustomer(), false)){
					if(rq.getEvent().getEventName().equalsIgnoreCase(r.getEvent().getEventName()))
						r.setId(rq.getId());
				}
			}
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	}
	
	public static void deleteRequest(boolean closeConnection, Request request) throws Exception{
	    String query = "DELETE "  +
	    		"FROM "+ DBWrapper.getDbname() +".budget_request " +
	    		"WHERE id_budget_request="+request.getId()+"";
	    
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        int rows = stmt.executeUpdate(query);
	        if(rows>1){
	        	throw new SQLException();
	        }else if(rows == 0)
	    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
	        EventWrapper.deleteEvent(false, request.getEvent());
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	}
	

}
